package com.info.zhiduoduo.repository.mapper.invoice;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.entrustmentproject.EntrustmentProjectItemInvoiceDto;
import com.info.zhiduoduo.repository.dto.invoice.*;
import com.info.zhiduoduo.repository.entity.invoice.Invoice;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author TerryJ
 * @since 2018-12-08
 */
public interface InvoiceMapper extends BaseMapper<Invoice> {

    @Select(" select i.*,c.creditcode " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " where i.compid = #{compid} " +
            " and i.state = #{state} " +
            " and i.project_type = #{projectType} " +
            " and IF(#{invoicetype}=0, 1=1, i.invoicetype = #{invoicetype}) " +
            " and concat(i.projectname,i.username) like '%${name}%' " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getBriefListByCompid(@Param("projectType") int projectType, @Param("compid") int compid,
                                               @Param("name") String name, @Param("invoicetype") int invoicetype,
                                               @Param("state") int state);

    @Select(" select i.id,i.projectid,i.projectname,i.state,i.project_type,i.invoice_amount,i.userid,i.username,i.compid,i.compname,i.createtime, " +
            " ma.realname " +
            " from zdd_invoice i  " +
            " join zdd_company c on c.compid = i.compid" +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " where i.project_type = 5 " +
            " and concat(i.projectname,i.username) like '%${name}%'" +
            " and i.compid = #{compid}" +
            " and IF(#{state} = 0, 1=1, i.state = #{state}) " +
            " and i.createtime between #{from} and #{to} " +
            " order by i.id desc ")
    List<EntrustmentProjectItemInvoiceDto> getEntrustInvoiceListByCompid(@Param("projectType") int projectType, @Param("compid") int compid,
                                                                         @Param("name") String name,
                                                                         @Param("state") int state, @Param("from") Date from, @Param("to") Date to);

    @Select(" select i.*,c.creditcode " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " where i.compid = #{compid} " +
            " and i.project_type = #{projectType} " +
            " and IF(ISNULL(#{states}) or #{states} = '', 1=1, i.state in(${states})) " +
            " and IF(#{invoicetype}=0, 1=1, i.invoicetype = #{invoicetype}) " +
            " and concat(i.projectname,i.username) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getBriefListByCompidAndStates(@Param("projectType") int projectType, @Param("compid") int compid,
                                               @Param("name") String name, @Param("invoicetype") int invoicetype,
                                               @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                               @Param("states") String states);


    @Select(" select i.*,c.creditcode from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " where i.userid = #{userid} " +
            " and i.state = #{state} " +
            " and i.project_type != 5 " +
            " and IF(#{invoicetype}=0, 1=1, i.invoicetype = #{invoicetype}) " +
            " and concat(i.projectname,i.username) like '%${name}%' " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getBriefListByUserid(@Param("userid") int userid, @Param("name") String name,
                                               @Param("invoicetype") int invoicetype, @Param("state") int state);

    @Select(" select i.*,tc.name as taxCityName,ma.opening_location,mop.name as openingLocationName,c.creditcode,ms.tax_acct_state,ca.strategy_invoice compStrategyInvoice,ma.temporary_login, " +
            " ca.realname,c2.compid as actualSender,c2.compname as actualCompanyName,ccp.company_name as subjectName,pay.pay_name, " +
            " case when (i.apply_invoice_date <= DATE_ADD(curdate(),interval -day(curdate())+1 day) or i.apply_invoice_date is null) then 1 else 0 end unInvoiceRemind " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid" +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " left join zdd_payment pay on i.pay_id = pay.id " +
            " left join zdd_company c2 on c2.compid = i.actual_company_id" +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = i.party_id " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " where IF(i.state = 0, 1=1, i.state = #{state}) " +
            " and if(isnull(#{projectType}) or #{projectType}='',1=1,i.project_type in(0${projectType}))  " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and IF(i.strategy = 1 and i.state = 1, DATE(i.strategy_date) <= CURDATE(), 1=1) " +
            " and if(isnull(#{temporaryLogin}),1=1,ma.temporary_login = #{temporaryLogin}) " +
            " and if(#{partyId} = 0, 1=1, i.party_id = #{partyId}) " +
            " and if(#{companyId} = 0,1=1,i.compid = #{companyId})" +
            " ${dockingCity} " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getBriefList(@Param("projectType") String projectType, @Param("name") String name, @Param("state") int state,
                                       @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                       @Param("dockingCity") String dockingCity, @Param("temporaryLogin") Integer temporaryLogin,
                                       @Param("partyId") int partyId,@Param("companyId") int companyId);

    @Select(" select i.*,tc.name as taxCityName,ma.opening_location,mop.name as openingLocationName,c.creditcode,ms.tax_acct_state," +
            " ca.strategy_invoice compStrategyInvoice,ma.temporary_login,ccp.company_name as subjectName " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = i.party_id " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " where i.state = 1 " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and i.strategy = 1 " +
            " and if(isnull(#{projectType}) or #{projectType}='',1=1,i.project_type in(0${projectType})) " +
            " AND i.strategy_date > CURRENT_DATE " +
            " and if(#{companyId} = 0,1=1,c.compid = #{companyId}) " +
            " ${dockingCity} " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getStrategyBriefList(@Param("name") String name, @Param("startDate") Date startDate,
                                               @Param("endDate") Date endDate,@Param("dockingCity") String dockingCity,
                                               @Param("projectType") String projectType,@Param("companyId") int companyId);

    @Select(" select i.*,tc.name as taxCityName,ma.opening_location,mop.name as openingLocationName,c.creditcode,ms.tax_acct_state,ca.strategy_invoice compStrategyInvoice, " +
            " ccp.company_name as subjectName,cp.as_protocol " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " left join zdd_crowdsource_project cp on cp.projectid = i.projectid and i.project_type = 2 " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = i.party_id " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " where i.state = 1 " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and i.strategy = 1 " +
            " and i.project_type = 2 " +
            " AND i.strategy_date > CURRENT_DATE " +
            " and if(#{companyId} = 0,1=1,c.compid = #{companyId}) " +
            " order by i.id desc ")
    List<InvoiceBriefDto> getCrowdsourceStrategyBriefList(@Param("name") String name, @Param("startDate") Date startDate,
                                               @Param("endDate") Date endDate,@Param("companyId") int companyId);

    @Select(" select i.*,c.creditcode,c.contactname,c.contactphone,ca.strategy_invoice compStrategyInvoice " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " where i.id = #{id}")
    InvoiceDetailDto getInvoinceDetail(@Param("id") Integer id);


    // 按完成日期 查询 众包项目 最近一次发单的“开票内容”
    //@Select(" select * from zdd_invoice where  compid = #{compid} and state = 4 and finishtime is not null and project_type = 2  order by finishtime desc limit 1 ")
    @Select(" SELECT i.* from zdd_invoice i LEFT JOIN zdd_crowdsource_project p ON i.projectid = p.projectid " +
            " WHERE i.project_type = 2 " +
            " AND i.compid = #{compid} " +
            " AND p.state = 4 " +
            " ORDER BY p.createtime DESC " +
            " LIMIT 1 ")
    InvoiceDetailDto queryLastInvoice(@Param("compid")Integer compid);


    @Select(" select i.* " +
            " from zdd_invoice i " +
            " where i.projectid = #{projectid} and i.project_type = #{projectType} ")
    Invoice findByProjectid(Integer projectid, int projectType);

    @Select(" select i.* " +
            " from zdd_invoice i " +
            " where i.offline_pay_state = 2 " +
            " and i.pay_id = 2 " +
            " and i.project_type = 1 ")
    List<Invoice> getOfflinePaidList();

    @Select(" select DATE(i.finishtime) as date,COUNT(i.id) as count " +
            " from zdd_invoice i " +
            " where i.state = 4 " +
            " and DATE(finishtime) >= '2020-01-01' " +
            " group by DATE(i.finishtime)")
	List<CountByDate> countByDate();

    @Select(" select i.* " +
            " from zdd_invoice i" +
            " where i.state != 4 " +
            " and i.project_type = 1 ")
    List<Invoice> getUnfinishList();

    @Select(" select SUM(invoice_amount) " +
            " from zdd_invoice " +
            " where userid = #{userid} " +
            " and state != 4 " +
            " and project_type = 1 " +
            " and IF(#{invoicetype} = 0, 1=1, invoicetype = #{invoicetype})")
    BigDecimal sumUnfinishInvoiceAmount(@Param("userid") Integer userid, @Param("invoicetype") int invoicetype);


    /**
     * 查询 主体企业（众包项目） 待开票 总金额
     * @param companyId 主体企业 id
     * @param invoicetype 发票类型
     * @return
     */
    @Select("  select SUM(i.invoice_amount) " +
            " from zdd_invoice i " +
            " left join zdd_crowdsource_project cp on cp.projectid = i.projectid and i.project_type = 2  " +
            " where i.party_id = #{companyId}  " +
            " and ( i.state=2 or i.state=3 )  " +
            " and i.project_type = 2 " +
            " and IF(#{invoicetype} = 0, 1=1, i.invoicetype = #{invoicetype}) ")
    BigDecimal sumNotFinishCompanyInvoiceAmount(@Param("companyId") Integer companyId, @Param("invoicetype") int invoicetype);



    /**
     * 该创客目前参与的 未开票的 项目个数（除去已删除的项目）
     * @param userid 创客userid
     * @return int
     */
    @Select(" SELECT count( * )  FROM zdd_invoice i " +
            " LEFT JOIN zdd_company_project p ON p.projectid = i.projectid " +
            " AND i.project_type = 1 " +
            " WHERE i.state != 4  AND i.userid = #{userid} AND p.state != 44")
    int notProjectInvoicedCountByUserid(@Param("userid") int userid);


    //之前年份月度开票金额统计
    @Select("select sum(if(month(inv.finishtime)=1 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as january," +
            "       sum(if(month(inv.finishtime)=2 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as february," +
            "       sum(if(month(inv.finishtime)=3 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as march," +
            "       sum(if(month(inv.finishtime)=4 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as april," +
            "       sum(if(month(inv.finishtime)=5 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as may," +
            "       sum(if(month(inv.finishtime)=6 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as june," +
            "       sum(if(month(inv.finishtime)=7 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as july," +
            "       sum(if(month(inv.finishtime)=8 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as august," +
            "       sum(if(month(inv.finishtime)=9 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as september," +
            "       sum(if(month(inv.finishtime)=10 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as october," +
            "       sum(if(month(inv.finishtime)=11 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as november," +
            "       sum(if(month(inv.finishtime)=12 and year(inv.finishtime)=#{year},inv.invoice_amount,0)) as december" +
            "       from zdd_invoice inv" +
            "       left join zdd_company_project pro on inv.projectid=pro.projectid" +
            "       where inv.project_type=#{project_type} and inv.state=4")
    List<InvoiceAmountDto> getInvoiceMakerAmountBefore(@Param("project_type") Integer project_type,@Param("year") Integer year);
    //之后年份月度开票金额统计
    @Select("select sum(if(month(inv.strategy_date)=1 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as january," +
            "       sum(if(month(inv.strategy_date)=2 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as february," +
            "       sum(if(month(inv.strategy_date)=3 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as march," +
            "       sum(if(month(inv.strategy_date)=4 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as april," +
            "       sum(if(month(inv.strategy_date)=5 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as may," +
            "       sum(if(month(inv.strategy_date)=6 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as june," +
            "       sum(if(month(inv.strategy_date)=7 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as july," +
            "       sum(if(month(inv.strategy_date)=8 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as august," +
            "       sum(if(month(inv.strategy_date)=9 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as september," +
            "       sum(if(month(inv.strategy_date)=10 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as october," +
            "       sum(if(month(inv.strategy_date)=11 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as november," +
            "       sum(if(month(inv.strategy_date)=12 and year(inv.strategy_date)=#{year},inv.invoice_amount,0)) as december" +
            "       from zdd_invoice inv" +
            "       left join zdd_company_project pro on inv.projectid=pro.projectid" +
            "       where inv.project_type=#{project_type} and inv.state=1 and inv.strategy=1")
    List<InvoiceAmountDto> getInvoiceMakerAmountAfter(@Param("project_type") Integer project_type,@Param("year") Integer year);
    //当前年份月度开票金额统计
    @Select("select " +
            "sum(if(month(now())>=1,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=1 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=1 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as january," +
            "sum(if(month(now())>=2,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=2 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=2 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as february," +
            "sum(if(month(now())>=3,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=3 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=3 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as march," +
            "sum(if(month(now())>=4,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=4 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=4 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as april," +
            "sum(if(month(now())>=5,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=5 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=5 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as may," +
            "sum(if(month(now())>=6,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=6 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=6 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as june," +
            "sum(if(month(now())>=7,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=7 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=7 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as july," +
            "sum(if(month(now())>=8,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=8 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=8 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as august," +
            "sum(if(month(now())>=9,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=9 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=9 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as september," +
            "sum(if(month(now())>=10,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=10 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=10 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as october," +
            "sum(if(month(now())>=11,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=11 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=11 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as november," +
            "sum(if(month(now())>=12,if(inv.state=4 and inv.strategy=0 and month(inv.finishtime)=12 and year(inv.finishtime)=year(now()),inv.invoice_amount,0)," +
            "if(inv.state=1 and inv.strategy=1 and month(inv.strategy_date)=12 and year(inv.strategy_date)=year(now()),inv.invoice_amount,0))) as december" +
            "from zdd_invoice inv" +
            "left join zdd_company_project pro on inv.projectid=pro.projectid" +
            "where inv.project_type=#{project_type}")
    List<InvoiceAmountDto> getInvoiceMakerAmount(@Param("project_type") Integer project_type);


    @Select("SELECT * FROM  zdd_invoice WHERE managerid = #{managerId} AND DATE(createtime) = DATE(#{date})")
    List<Invoice> selectListByManagerIdAndDate(@Param("managerId") Integer managerId,@Param("date") Date date);

    @Select(" SELECT COUNT(*) from zdd_invoice i " +
            " WHERE i.compid = #{compid} " +
            " AND i.project_type = 2 " +
            " AND i.state in(3,4) " +
            " AND YEAR(i.finishtime) = YEAR(#{month}) " +
            " AND MONTH(i.finishtime) = MONTH(#{month})")
	int countFinishedCompanyCrowdsourceInvoice(@Param("compid") Integer compid, @Param("month") Date month);

    @Select("  select stu.username,if(isnull(a.invoiceAmount),0,a.invoiceAmount) makerSum,if(isnull(b.projectAmount),0,b.projectAmount)costSum ," +
            " if(isnull(c.amounts),0,c.amounts) taxerSum, " +
            " if(isnull(d.invoiceAmount),0,d.invoiceAmount)+if(isnull(e.projectAmount),0,e.projectAmount)+if(isnull(f.amounts),0,f.amounts) allAmounts , " +
            " if(isnull(a.invoiceAmount),0,a.invoiceAmount)+if(isnull(b.projectAmount),0,b.projectAmount)+if(isnull(c.amounts),0,c.amounts) sum from " +
            " zdd_maker_studio stu left join " +
            " ( select sum(i.invoice_amount) invoiceAmount,ms.userid " +
            " from zdd_invoice i " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " where i.state in(3,4) and i.project_type in(1,4,5,7,8)  " +
//            " and i.createtime BETWEEN concat(DATE_FORMAT(date_add(NOW(),interval - 11 month), '%Y-%m'),'-01') and DATE_FORMAT(now(), '%Y-%m-%d') " +
           "  AND DATE_FORMAT(i.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by ms.userid ) a " +
            " on a.userid=stu.userid " +
            " left join  " +
            " (  SELECT sum(zci.project_amount) projectAmount,zms.userid " +
            "    FROM zdd_crowdsource_invoice zci " +
            "    LEFT JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid AND zci.user_type = 2 " +
            "    WHERE zci.user_type = 2 AND  zci.state in(2,3) " +
//            " and zci.createtime BETWEEN concat(DATE_FORMAT(date_add(NOW(),interval - 11 month), '%Y-%m'),'-01') and DATE_FORMAT(now(), '%Y-%m-%d') " +
            " AND DATE_FORMAT(zci.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by zms.userid ) b on b.userid=stu.userid " +
            " left join " +
            " (select sum(ti.amount) amounts,ms.userid " +
            " from zdd_taxer_invoice ti " +
            "  join zdd_maker_studio ms on ms.userid = ti.userid " +
            " where ti.state = 2 and ti.deleted = 0 " +
//            " and ti.createtime BETWEEN concat(DATE_FORMAT(date_add(NOW(),interval - 11 month), '%Y-%m'),'-01') and DATE_FORMAT(now(), '%Y-%m-%d') " +
            " AND DATE_FORMAT(ti.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by ms.userid) c on c.userid=stu.userid " +
            " left join ( select sum(i.invoice_amount) invoiceAmount,ms.userid " +
            " from zdd_invoice i " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " where i.state in(1,2,3,4) and i.project_type in(1,4,5,7,8)  " +
            "  AND DATE_FORMAT(i.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by ms.userid ) d " +
            " on d.userid=stu.userid " +
            " left join  " +
            " (  SELECT sum(zci.project_amount) projectAmount,zms.userid " +
            "    FROM zdd_crowdsource_invoice zci " +
            "    LEFT JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid AND zci.user_type = 2 " +
            "    WHERE zci.user_type = 2 AND  zci.state in(1,2,3) " +
            " AND DATE_FORMAT(zci.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by zms.userid ) e on e.userid=stu.userid " +
            " left join " +
            " (select sum(ti.amount) amounts,ms.userid " +
            " from zdd_taxer_invoice ti " +
            "  join zdd_maker_studio ms on ms.userid = ti.userid " +
            " where ti.state in(1,2) and ti.deleted = 0 " +
            " AND DATE_FORMAT(ti.createtime,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') " +
            " group by ms.userid) f on f.userid=stu.userid " +
            " where if(isnull(#{condition}) or #{condition}='',1=1,stu.username like concat('%',#{condition},'%')) " +
            " order by stu.createtime desc ")
    List<QueryMakerInvoiceSumPriceDto> querymakerInvoiceSumPrice(@Param("condition") String condition);


    @Select(" select i.id,i.projectid,i.projectname,i.project_type,i.invoicetype,i.invoice_amount,i.content,i.userid,i.username, " +
            " i.compid,i.compname,i.state,i.pay_amount,i.pay_receipt,i.createtime,i.projectfinishtime,i.finishtime,i.managerid,i.pay_id, " +
            " tc.name as taxCityName,mop.name as openingLocationName,ma.temporary_login, " +
            " opr.offline_remark,opr.pay_voucher,opr.adminid,sm.managername as adminName " +
            " from zdd_invoice i " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " left join zdd_invoice_offline_pay_record opr on i.id = opr.invoice_id " +
            " left join zdd_system_manager sm on sm.managerid = opr.adminid" +
            " where i.pay_id = 2 " +
            " and if(isnull(#{invoiceState}),1=1,i.state = #{invoiceState})" +
            " and if(isnull(#{offlinePayState}) or #{offlinePayState}='',1=1,i.offline_pay_state in(0${offlinePayState})) " +
            " and if(isnull(#{projectType}) or #{projectType}='',1=1,i.project_type in(0${projectType}))  " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and IF(i.strategy = 1 and i.state = 1, DATE(i.strategy_date) <= CURDATE(), 1=1) " +
            " and if(isnull(#{temporaryLogin}),1=1,ma.temporary_login = #{temporaryLogin}) " +
            " order by i.id desc ")
    List<QueryInvoiceDto> queryApplyInvoiceList(@Param("name") String name, @Param("offlinePayState") String offlinePayState,@Param("startDate") Date startDate,
                                                @Param("endDate") Date endDate,@Param("temporaryLogin") Integer temporaryLogin,@Param("projectType") String projectType,
                                                @Param("invoiceState") Integer invoiceState);

    @Select(" select * from zdd_invoice where finishtime = #{date} and state = 4")
	List<Invoice> listFinishedByDate(@Param("date") Date date);

    @Select(" SELECT id FROM ( " +
            " SELECT * from zdd_invoice i " +
            " WHERE i.userid = #{userid} " +
            " GROUP BY i.createtime asc " +
            " ) as base " +
            "GROUP BY userid ")
    Integer firstBilling(@Param("userid") int userid);

    @Select("select " +
            " if(isnull(a.invoiceAmount),0,a.invoiceAmount)+ " +
            " if(isnull(b.projectAmount),0,b.projectAmount)+ " +
            " if(isnull(d.invoiceAmount),0,d.invoiceAmount)+ " +
            " if(isnull(e.projectAmount),0,e.projectAmount)+ " +
            " if(isnull(f.amounts),0,f.amounts) AS allAmounts " +
            " from  " +
            " zdd_maker_studio stu " +
            " left join " +
            " (select sum(i.invoice_amount) invoiceAmount,ms.userid  " +
            "   from zdd_invoice i  " +
            "   left join zdd_maker_studio ms on ms.userid = i.userid  " +
            "   where i.state = 3 and i.project_type in(1,4,5,7,8)   " +
            "   group by ms.userid ) a on a.userid=stu.userid  " +
            "  left join   " +
            "  (SELECT sum(zci.project_amount) projectAmount,zms.userid  " +
            "    FROM zdd_crowdsource_invoice zci  " +
            "    LEFT JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid AND zci.user_type = 2  " +
            "    WHERE zci.user_type = 2 AND  zci.state = 2 " +
            "    group by zms.userid ) b on b.userid=stu.userid  " +
            "  left join " +
            "  (select sum(i.invoice_amount) invoiceAmount,ms.userid  " +
            "   from zdd_invoice i  " +
            "   left join zdd_maker_studio ms on ms.userid = i.userid  " +
            "   where i.state = 4 and i.project_type in(1,4,5,7,8)   " +
            "   and YEAR(i.finishtime) = YEAR(Now()) " +
            "   and QUARTER(i.finishtime) = QUARTER(Now())" +
            "   group by ms.userid ) d  on d.userid=stu.userid  " +
            "  left join   " +
            "   (SELECT sum(zci.project_amount) projectAmount,zms.userid  " +
            "    FROM zdd_crowdsource_invoice zci  " +
            "    LEFT JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid AND zci.user_type = 2  " +
            "    WHERE zci.user_type = 2 AND  zci.state = 3" +
            "    and YEAR(zci.finishtime) = YEAR(Now()) " +
            "    and QUARTER(zci.finishtime) = QUARTER(Now())" +
            "    group by zms.userid ) e on e.userid=stu.userid " +
            "   left join  " +
            "     (select sum(ti.amount) amounts,ms.userid  " +
            "      from zdd_taxer_invoice ti  " +
            "      join zdd_maker_studio ms on ms.userid = ti.userid  " +
            "      where ti.state = 2  and ti.deleted = 0  " +
            "      and YEAR(ti.confirmtime) = YEAR(Now()) " +
            "      and QUARTER(ti.confirmtime) = QUARTER(Now())" +
            "      group by ms.userid) f on f.userid=stu.userid  " +
            "  where stu.userid = #{userId} ")
    BigDecimal obtainQuarterlyAmount(@Param("userId") Integer userId);

    @Select(" select i.*,tc.name as taxCityName,ma.opening_location,mop.name as openingLocationName,c.creditcode, " +
            " ca.realname,ccp.company_name as subjectName " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid" +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
//            " left join zdd_crowdsource_project cp on cp.projectid = i.projectid " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = i.party_id " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " where IF(i.state = 0, 1=1, i.state = #{state}) " +
            " and if(isnull(#{projectType}) or #{projectType}='',1=1,i.project_type in(0${projectType}))  " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and if(#{partyId} = 0, 1=1, i.party_id = #{partyId}) " +
            " and i.compid = #{compid}" +
            " order by i.id desc ")
    List<InvoiceBriefDto> enterpriseBriefList(@Param("projectType") String projectType, @Param("name") String name, @Param("state") int state,
                                       @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                       @Param("compid") int compid,@Param("partyId") int partyId);

    @Select(" select i.*,tc.name as taxCityName,ma.opening_location,mop.name as openingLocationName,c.creditcode,ms.tax_acct_state,ca.strategy_invoice compStrategyInvoice, " +
            " ccp.company_name as subjectName,cp.as_protocol " +
            " from zdd_invoice i " +
            " join zdd_company c on c.compid = i.compid " +
            " join zdd_company_account ca on ca.compid = i.compid " +
            " left join zdd_maker_studio ms on ms.userid = i.userid " +
            " left join zdd_maker_account ma on ma.userid = i.userid " +
            " left join zdd_crowdsource_project cp on cp.projectid = i.projectid and i.project_type = 2 " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = i.party_id " +
            " left join zdd_city_config tc on tc.id = i.tax_city " +
            " left join zdd_city_config mop on mop.id = ma.opening_location " +
            " where i.state = 1 " +
            " and concat(i.projectname,i.username,i.compname) like '%${name}%' " +
            " and i.createtime between #{startDate} and #{endDate} " +
            " and i.strategy = 1 " +
            " and i.project_type = 2 " +
            " AND i.strategy_date > CURRENT_DATE " +
            " and i.compid = #{compid}" +
            " order by i.id desc ")
    List<InvoiceBriefDto> enterpriseCrowdSourceStrategyBriefList (@Param("name") String name, @Param("startDate") Date startDate,
                                                          @Param("endDate") Date endDate,@Param("compid") int compid);

    @Select(" select sum(amount) from (" +
            " select IFNULL(SUM(i.invoice_amount), 0) as amount " +
            " from zdd_invoice i " +
            " where i.state = 1 and i.userid = #{userId} " +
            " and i.strategy = 1 and i.project_type in (1,2) " +
            " and i.strategy_date > CURRENT_DATE " +
            " union all" +
            " select IFNULL(SUM(ci.project_amount), 0) as amount " +
            " from zdd_crowdsource_invoice ci " +
            " where ci.state = 1 and ci.crowdsourceid = #{userId}" +
            " and ci.strategy = 1 and ci.project_type in (1,2) " +
            " and ci.strategy_date > CURRENT_DATE" +
            " )invoice")
    BigDecimal getStrategyInvoiceAmount(Integer userId);

    //查询创客当前季度策略性开票总金额
    @Select("select sum(amount) from (" +
            " select IFNULL(SUM(i.invoice_amount), 0) as amount " +
            " from zdd_invoice i " +
            " where i.state = 1 and i.userid = #{userId} " +
            " and i.strategy = 1 and i.project_type in (1,4,5,7) " +
            " and YEAR(i.createtime) = YEAR(NOW()) and QUARTER(i.createtime) = QUARTER(NOW())" +
            " union all" +
            " select IFNULL(SUM(ci.project_amount), 0) as amount " +
            " from zdd_crowdsource_invoice ci " +
            " where ci.state = 1 and ci.crowdsourceid = #{userId}" +
            " and ci.strategy = 1 and ci.project_type in (2,6,8) " +
            " and YEAR(ci.createtime) = YEAR(NOW()) and QUARTER(ci.createtime) = QUARTER(NOW())" +
            " )invoice ")
    BigDecimal getUserQuarterStrategyInvoicingAmount(Integer userId);

    //创客待开票总金额
    @Select("select sum(amount) from (" +
            " select IFNULL(SUM(i.invoice_amount), 0) as amount " +
            " from zdd_invoice i " +
            " where i.state in (1,2,3) and i.userid = #{userId} " +
            " and i.strategy = 0 and i.project_type in (1,4,5,7) " +
            " union all" +
            " select IFNULL(SUM(ci.project_amount), 0) as amount " +
            " from zdd_crowdsource_invoice ci " +
            " where ci.state in (1,2) and ci.crowdsourceid = #{userId} " +
            " and ci.strategy = 0 and ci.project_type in (2,6,8) " +
            " )invoice")
    BigDecimal getUserInvoicingAmount(Integer userId);

    /**
     * @param timeType 时间类型（1：当前月度，2：当前季度，3：当前年度，4：当前时间往前的12个月）
     */
    @Select("select IFNULL(SUM(i.invoice_amount),0) " +
            " from zdd_invoice i " +
            " where i.project_type in (0${projectTypes}) " +
            " and i.state = 4 " +
            " and i.userid = #{userId} " +
            " and " +
            "   CASE  " +
            "   when #{timeType} = 1 then YEAR(i.finishtime) = YEAR(NOW()) and MONTH(i.finishtime) = MONTH(NOW()) " +
            "   when #{timeType} = 2 then YEAR(i.finishtime) = YEAR(NOW()) and QUARTER(i.finishtime) = QUARTER(NOW())" +
            "   when #{timeType} = 3 then YEAR(i.finishtime) = YEAR(NOW()) " +
            "   when #{timeType} = 4 then i.finishtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH)" +
            "   END")
    BigDecimal getUserCompleteAmount(Integer userId, String projectTypes, Integer timeType);

}
